import pymysql
from DBUtils.PooledDB import PooledDB

class GetPool:
    def getPool(self):
        maxConnections = 10
        pool = PooledDB(
            pymysql,
            maxConnections,
            host="localhost",
            user="root",
            port=3306,
            passwd="root")
        return pool

globalPool = GetPool().getPool()

class MyDBUtils:
    def __init__(self, database, sentence, infos, isDict=0):
        self.database = database
        self.sentence = sentence
        self.infos = infos
        self.isDict = isDict
        self.pool = globalPool

    def createDB(self):
        try:
            conn = self.pool.connection()
            cur = conn.cursor()
            # 提前把数据库名拼接好
            sql = "CREATE DATABASE IF NOT EXISTS " + self.database + ";"
            res = cur.execute(sql)
            conn.commit()
            return "success"
        except Exception as e:
            raise e
        finally:
            cur.close()
            conn.close()

    def createTable(self):
        try:
            self.createDB()
            conn = self.pool.connection()
            cur = conn.cursor()
            sql = "USE " + self.database+";"
            cur.execute(sql)
            cur.execute(self.sentence)
            conn.commit()
            return "success"
        except Exception as e:
            print(e)
            return "fail"
        finally:
            cur.close()
            conn.close()

    def selectData(self):
        try:
            self.createDB()
            conn = self.pool.connection()
            if self.isDict:
                cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
            else:
                cur = conn.cursor()
            sql = "USE " + self.database+";"
            cur.execute(sql)
            if self.infos:
                cur.execute(self.sentence,self.infos)
            else:
                cur.execute(self.sentence)
            return cur.fetchall()
        except Exception as e:
            print(e)
            return 'fail'
        finally:
            cur.close()
            conn.close()

    def updateData(self):
        try:
            self.createDB()
            conn = self.pool.connection()
            cur = conn.cursor()
            sql = "USE " + self.database+";"
            cur.execute(sql)
            if self.infos:
                cur.execute(self.sentence, self.infos)
                conn.commit()
            else:
                cur.execute(self.sentence)
                conn.commit()
            return "success"
        except Exception as e:
            print(e)
            conn.rollback()
            return "fail"
        finally:
            cur.close()
            conn.close()
